package datamodule;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import util.FString;
import conexao.Connect;

public class CargosDAO {


	private DBuserVO userVO;

	public CargosDAO( DBuserVO userVO){
		this.userVO = userVO;
	}
	//Inserir Cargos
	public int inserir(CargosVO VOcar) throws ClassNotFoundException, SQLException {
		Connection con = Connect.getConnection(userVO);
		PreparedStatement pstmtInsert = con.prepareStatement(
				"INSERT INTO cargos(car_Cod_Cargo, car_Descricao) " +
				" VALUES(?,?) " );
		pstmtInsert.setString(1, VOcar.getCar_Cod_Cargo());			
		pstmtInsert.setString(2, VOcar.getCar_Descricao());
		int reg = pstmtInsert.executeUpdate();	
		con.close();
		return reg;
	}

	//Alterar Cargos
	public int alterar(CargosVO VOcar) throws ClassNotFoundException, SQLException {
		Connection con = Connect.getConnection(userVO);
		String virgula = "";
		StringBuffer sql = new StringBuffer(" UPDATE cargos SET ");
		if (!VOcar.getCar_Cod_Cargo().equals(FString.ignorar)) {
			sql.append(virgula+" car_Cod_Cargo = '"+VOcar.getCar_Cod_Cargo()+"' ");
			virgula = ",";
		}
		if (!VOcar.getCar_Descricao().equals(FString.ignorar)) {
			sql.append(virgula+" car_Descricao = '"+VOcar.getCar_Descricao()+"' ");
			virgula = ",";
		}

		sql.append(" WHERE car_Cod_Cargo = '"+VOcar.getCar_Cod_Cargo()+"' ");
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0, sql.length()));
		int registrosAfetados = pstmt.executeUpdate();
		con.close();
		return registrosAfetados;
	}	
	
	// Deletar Cargos
	public int deletar(CargosVO VOcar) throws ClassNotFoundException, SQLException {		
		Connection con = Connect.getConnection(userVO);
		PreparedStatement pstmt = con.prepareStatement(
		"DELETE FROM cargos WHERE car_Cod_Cargo = ?");
		pstmt.setString(1, VOcar.getCar_Cod_Cargo());
		int registrosAfetados = pstmt.executeUpdate();
		con.close();
		return registrosAfetados;
	}

	//Pesquisar Cargos
	public CargosVO pesquisar(CargosVO VOcar) throws ClassNotFoundException, SQLException {				
		Connection con = Connect.getConnection(userVO);
		StringBuffer sql = new StringBuffer("SELECT * FROM cargos Where car_Cod_Cargo <> '' ");
		
		if (!VOcar.getCar_Cod_Cargo().equals(FString.ignorar)) {
			sql.append(" AND UPPER(car_Cod_Cargo) = '"+VOcar.getCar_Cod_Cargo()+"' ");			
		}		
		if (!VOcar.getCar_Descricao().equals(FString.ignorar)) {
			sql.append(" AND UPPER(car_Descricao) = '"+VOcar.getCar_Descricao()+"' ");			
		}
	
		//System.out.println(sql);
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0,sql.length()));		
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {

			VOcar = new CargosVO();
			VOcar.setCar_Cod_Cargo( rs.getString("car_Cod_Cargo"));
			VOcar.setCar_Descricao ( rs.getString("car_Descricao"));
			
		}else {
			VOcar = null;
		}
		pstmt.close();
		rs.close();
		con.close();
		return VOcar;
	}

	public ArrayList<CargosVO> geral(CargosVO VOcar) throws SQLException, ClassNotFoundException {		
		ArrayList<CargosVO> lista = null;
		Connection con = Connect.getConnection(userVO);
		StringBuffer sql = new StringBuffer("SELECT * FROM cargos  Where car_Cod_Cargo <> '' ");
		if (!VOcar.getCar_Cod_Cargo().equals(FString.ignorar)) {
			sql.append(" AND UPPER(car_Cod_Cargo) LIKE UPPER('"+VOcar.getCar_Cod_Cargo()+"%"+"')");
		}
		if (!VOcar.getCar_Descricao().equals(FString.ignorar)) {
			sql.append(" AND UPPER(car_Descricao) LIKE UPPER('"+VOcar.getCar_Descricao()+"%"+"')");
		}
		if (!VOcar.getOrdem().equals(FString.ignorar)) {
			sql.append(" ORDER BY "+VOcar.getOrdem());
		}
		if (!VOcar.getLimit().equals(FString.ignorar)) {
			sql.append(" LIMIT "+VOcar.getLimit());
		}
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0,sql.length()));		
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			lista = new ArrayList<CargosVO>();
			do {
				CargosVO his = new CargosVO();
				his.setCar_Cod_Cargo( rs.getString("car_Cod_Cargo"));
				his.setCar_Descricao( rs.getString("car_Descricao"));
			
				lista.add(his);
			}
			while (rs.next());			
		}
		pstmt.close();
		rs.close();
		con.close();
		return lista;
	}	
	
}